package cn.com.dashihui.web.service;

import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.com.dashihui.kit.ExcelExportUtil;
import cn.com.dashihui.kit.ExcelExportUtil.Pair;
import cn.com.dashihui.web.dao.Store;
import cn.com.dashihui.web.dao.User;
import cn.com.dashihui.web.dao.UserChange;
import cn.com.dashihui.web.dao.UserChangeExcel;

public class ChangeSyncService {

	public Page<Record> findByPage(int pageNum, int pageSize,String phone, String card, int match){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_user A "
				+ "left join t_bus_user_change D on A.phone = D.linkPhone "
				+ "left join t_bus_user_bankid C on A.bankNoId = C.bankId "
				+ "left join t_auth_admin_seller B on A.toBossOneId = B.id "
				+ "left join t_dict_store E on A.toBossThreeId = E.id "
				+ " where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		
		switch (match) {
		case 1:
			sBuffer.append(" and D.match=1 ");
			break;
		case 2:
			sBuffer.append(" and D.clientCode is null ");
			break;
		}
		sBuffer.append(" ORDER BY A.createDate DESC,A.id desc");
		return Db.paginate(pageNum, pageSize, "SELECT A.*,B.trueName ,C.bankName,D.clientCode,E.title", sBuffer.toString(),params.toArray());
	}
	/**
	 * 查询用户详情
	 * @param id
	 * @return
	 */
	public User findById(int id){
		return User.me().findFirst("SELECT A.* ,ifnull(C.countcum,0) countnum ,ifnull(C.allCount,0) allCount,  "
				+ " (year(now())-year(A.birthday)-1) + ( DATE_FORMAT(A.birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') ) as age "
				+ " FROM t_bus_user A left join t_bus_user_wx B on A.id=B.userid left join (select userid,count(id) as countcum,sum(amount) as allCount from "
				+ " t_bus_order where orderState=2 group by userid) C on C.userid= B.id WHERE A.id=?",id);
	}
	/**
	 * 查询所有分公司
	 * @return
	 */
	public List<Store> findStoreList() {
		return Store.me().find("select id,storeid,title,city from t_dict_store");
	}
	/**
	 * 根据录入的条件查询所有的user
	 * @param phone
	 * @param card
	 * @param title
	 * @return
	 */
	public List<Record> findExcelByPage(String phone, String card, int match) {
		StringBuffer sBuffer = new StringBuffer("SELECT A.*,B.bankName,C.clientCode,D.cardNo as bankCardNo FROM t_bus_user A "
				+ "left join t_bus_user_change C on A.phone = C.linkPhone "
				+ "left join t_bus_user_bankid B on A.bankNoId = B.bankId "
				+ "left join t_bus_user_card D on D.userid = A.id "
				+ " where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		switch (match) {
		case 1:
			sBuffer.append(" and D.match=1 ");
			break;
		case 2:
			sBuffer.append(" and D.clientCode is null ");
			break;
		}
		
		
		sBuffer.append(" ORDER BY A.createDate DESC,A.id desc");
		return Db.find(sBuffer.toString(),params.toArray());
	}
	/**
	 * 导出
	 * @param response
	 * @param request
	 * @param titles
	 * @param listz
	 */
	public void export(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "券票转换&会员同步", titles, listz);
	}
	/**
	 * 
	 * 根据clientName判重
	 * @param clientName
	 * @return
	 */
	public static  boolean checkTelephoneIsExist(String clientName) {
		return UserChange.me().findFirst("SELECT * FROM t_bus_user_change WHERE clientName=?",clientName)!=null;
	}
	/**
	 * 根据用户手机号查询用户
	 * @param newObject
	 * @return
	 */
	public User findByUserPhone(String phone){
		return User.me().findFirst("select * from t_bus_user where phone = ?", phone);
	}
	/**
	 * 查询所有操作日志
	 * @return
	 */
	public List<UserChangeExcel> findUserChangeExcel() {
		return UserChangeExcel.me().find("select * from t_bus_user_change_log");
	}
	/**
	 * 根据银行名称查询当前用户所属银行id
	 * @return
	 */
	public Record findBankIdByBankName(String bankName) {
		return Db.findFirst("select * from t_bus_user_bankid where bankName=?",bankName);
	}
	/**
	 * 查询操作日志，并返回
	 * @param pageSize 
	 * @param pageNum 
	 * @return
	 */
	public Page<Record> importLog(int pageNum, int pageSize) {
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_user_change_log A "
				+ " where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		
		sBuffer.append(" ORDER BY A.id desc");
		return Db.paginate(pageNum, pageSize, "SELECT A.*",sBuffer.toString(),params.toArray());
	}
}
